<?php
/**
 * Description of ExamBasicclass
 *
 * @author Chisheng Lu, Jeffy Shih
 * @copyright Copyright(c)2010 DoRISE team, Science Education center, NTNU.
 * @since 2010-05-20
 * @version 1.2
 */
class ExamBasic {
    //put your code here
    private $mysqli;

    function __construct() {
        //引入資料庫連結參數設定
        include '../inc/oaConn.inc.php';
        $this->mysqli = @new mysqli($mysqlHost,$mysqlUser,$mysqlPassword,$mysqlDB);
        if(mysqli_connect_errno()) {
            throw new Exception('無法建立資料庫連線！'.mysqli_connect_error());
            $this->mysqli = false;
            exit();
        }

        //連線使用編碼
        $this->mysqli->query("SET NAMES 'utf8'");
    }

    /**
     *
     * @param Array $aryExamBasic
     * @return Boolean
     */
    public function addExamBasic($aryExamBasic) {
        $intRtn = 0;
        $strNow = $this->getNowTime();
        $sql_addEB = 'INSERT INTO `ExamBasic` (';
        $sql_values = ' VALUES(';
        if (array_key_exists('EBTitle', $aryExamBasic)) {
            $sql_addEB .= '`EBTitle`, ';
            $sql_values .= '\'' . $aryExamBasic['EBTitle'] . '\', ';
        }
        if (array_key_exists('EBSubjectId', $aryExamBasic)) {
            $sql_addEB .= '`EBSubjectId`, ';
            $sql_values .= $aryExamBasic['EBSubjectId'] . ', ';
        }
        if (array_key_exists('EBGradeBegin', $aryExamBasic)) {
            $sql_addEB .= '`EBGradeBegin`, ';
            $sql_values .= $aryExamBasic['EBGradeBegin'] . ', ';
        }
        if (array_key_exists('EBGradeEnd', $aryExamBasic)) {
            $sql_addEB .= '`EBGradeEnd`, ';
            $sql_values .= $aryExamBasic['EBGradeEnd'] . ', ';
        }
        if (array_key_exists('EBReAnswer', $aryExamBasic)) {
            $sql_addEB .= '`EBReAnswer`, ';
            $sql_values .= $aryExamBasic['EBReAnswer'] . ', ';
        }
        if (array_key_exists('EBLock', $aryExamBasic)) {
            $sql_addEB .= '`EBLock`, ';
            $sql_values .= $aryExamBasic['EBLock'] . ', ';
        }
        if (array_key_exists('EBMessage', $aryExamBasic)) {
            $sql_addEB .= '`EBMessage`, ';
            $sql_values .= '\'' . $aryExamBasic['EBMessage'] . '\', ';
        }
        if (array_key_exists('EBNote', $aryExamBasic)) {
            $sql_addEB .= '`EBNote`, ';
            $sql_values .= '\'' . $aryExamBasic['EBNote'] . '\', ';
        }
        if (array_key_exists('UserId', $aryExamBasic)) {
            $sql_addEB .= '`UserId`, ';
            $sql_values .= $aryExamBasic['UserId'] . ', ';
        }
        $sql_addEB .= '`EBCreated` ';
        $sql_values .= '\'' . $strNow . '\' ';
        $sql_addEB .= ')' . $sql_values . ')';
        
        $rs_addEB = $this->mysqli->query($sql_addEB);
        if (!$rs_addEB) {
            throw new Exception('新增試卷基本資料時發生錯誤！');
        }else {
            $intRtn = $this->mysqli->insert_id;
        }
        return $intRtn;

    }

    /**
     * 
     * @param Integer $intEBId
     * @param Array $aryExamBasic
     * @return Boolean
     */
    public function editExamBasic($intEBId, $aryExamBasic) {
        //檢查試卷基本資料編號是否為空白
        if (empty ($intEBId)) {
            throw new Exception('缺少試卷基本資料編號！');
        }
        $bolRtn = false;
        $strNow = $this->getNowTime();
        $sql_editEB = 'UPDATE `ExamBasic` SET ';
        if (array_key_exists('EBTitle', $aryExamBasic)){
            $sql_editEB .= '`EBTitle` = \'' . $aryExamBasic['EBTitle'] . '\', ';
        }
        if (array_key_exists('EBSubjectId', $aryExamBasic)){
            $sql_editEB .= '`EBSubjectId` = ' . $aryExamBasic['EBSubjectId'] . ', ';
        }
        if (array_key_exists('EBGradeBegin', $aryExamBasic)){
            $sql_editEB .= '`EBGradeBegin` = ' . $aryExamBasic['EBGradeBegin'] . ', ';
        }
        if (array_key_exists('EBGradeEnd', $aryExamBasic)){
            $sql_editEB .= '`EBGradeEnd` = ' . $aryExamBasic['EBGradeEnd'] . ', ';
        }
        if (array_key_exists('EBReAnswer', $aryExamBasic)){
            $sql_editEB .= '`EBReAnswer` = ' . $aryExamBasic['EBReAnswer'] . ', ';
        }
        if (array_key_exists('EBMessage', $aryExamBasic)){
            $sql_editEB .= '`EBMessage` = \'' . $aryExamBasic['EBMessage'] . '\', ';
        }
        if (array_key_exists('EBNote', $aryExamBasic)){
            $sql_editEB .= '`EBNote` = \'' . $aryExamBasic['EBNote'] . '\', ';
        }

        $sql_editEB .= '`EBModifyUserId` = ' . $aryExamBasic['EBModifyUserId'] . ', ';
        $sql_editEB .= '`EBModified` = \'' . $strNow . '\' ';
        $sql_editEB .= 'WHERE `EBId` = ' . $intEBId . ';';
        
        $rs_editEB = $this->mysqli->query($sql_editEB);
        if (!$rs_editEB) {
            throw new Exception('更新試卷基本資料時發生錯誤！');
        }else {
            $bolRtn = true;
        }
        return $bolRtn;
    }

    /**
     *
     * @param Integer $intEBId
     * @return Boolean
     */
    public function delExamBasic($intEBId) {
        $bolRtn = false;
        //檢查試卷基本資料編號是否為空白
        if (empty ($intEBId)) {
            throw new Exception('錯誤！缺少試卷基本資料編號。');
        }

        //刪除
        $sql_delEB = 'DELETE FROM `ExamBasic` WHERE `EBId`=' . $intEBId;
        $rs_delEB = $this->mysqli->query($sql_delEB);
        if (!$rs_delEB) {
            throw new Exception('錯誤！刪除試卷基本資料時發生錯誤。');
        }else {
            $bolRtn = true;
        }

        return $bolRtn;

    }

    /**
     *
     * @param Integer $intEBId
     * @return Boolean: True: Locked, False: unLocked
     */
    public function chkExamLockByEBId($intEBId) {
        //檢查試卷基本資料編號是否為空白
        if (empty ($intEBId)) {
            throw new Exception('缺少試卷基本資料編號！');
        }
        $sql_getEBById = 'SELECT `EBLock` FROM `ExamBasic` WHERE `EBId`=' . $intEBId;
        $rs_getEBById = $this->mysqli->query($sql_getEBById);

        if (!$rs_getEBById) {
            throw new Exception('無法查詢該試卷基本資料的資料！');
        }else {
            if ($rs_getEBById->num_rows == 0) {
                throw new Exception('無此試卷基本資料編號的資料！');
            }else {
                $row_getEBById = $rs_getEBById->fetch_row();
                return ($row_getEBById[0] == 1);
            }
        } //End If

    }

    /**
     *
     * @param Integer $intEBId
     * @return Array
     */
    public function getExamBasicInfoByEBId($intEBId) {
        //檢查試卷基本資料編號是否為空白
        if (empty ($intEBId)) {
            throw new Exception('缺少試卷基本資料編號！');
        }

        $sql_getEBById = 'SELECT * FROM `ExamBasic` WHERE `EBId`=' . $intEBId;
        $rs_getEBById = $this->mysqli->query($sql_getEBById);

        if (!$rs_getEBById) {
            throw new Exception('無法查詢該試卷基本資料的資料！');
        }else {
            if ($rs_getEBById->num_rows == 0) {
                throw new Exception('無此試卷基本資料編號的資料！');
            }else {
                $row_getEBById = $rs_getEBById->fetch_row();
                return $row_getEBById;
            }
        } //End If

    }

    /**
     *
     * @return Array
     */
    public function getExamBasicList() {
        $sql_getEBList = 'SELECT * FROM `ExamBasic` ;';
        $rs_getEBList = $this->mysqli->query($sql_getEBList);

        if (!$rs_getEBList) {
            throw new Exception('無法查詢該試卷基本資料的資料！');
        }else {
            if ($rs_getEBList->num_rows == 0) {
                throw new Exception('無此試卷基本資料編號的資料！');
            }else {
                $aryEBList = array();
                while ($aryOneEB = $rs_getEBList->fetch_row()) {
                    array_push($aryEBList, $aryOneEB);
                }
                return $aryEBList;
            }
        } //End If

    }

    /**
     *
     * @param Integer $intUserId
     * @return Array
     */
    public function getExamBasicListByUserId($intUserId) {
        if ($intUserId <= 0) {
            throw new Exception('使用者編號錯誤！' . $intUserId);
        }
        $sql_getEBList = 'SELECT * FROM `ExamBasic` Where UserId = ' . $intUserId . ';';
        $rs_getEBList = $this->mysqli->query($sql_getEBList);

        if (!$rs_getEBList) {
            throw new Exception('無法查詢該試卷基本資料的資料！');
        }else {
            $aryEBList = array();
            while ($aryOneEB = $rs_getEBList->fetch_row()) {
                array_push($aryEBList, $aryOneEB);
            }
            return $aryEBList;
        } //End If

    }

    /**
     *
     * @since version 1.2 (2010-06-01)
     * @param Integer $intEBId
     * @return Boolean
     */
    public function setToTest($intEBId){
        //檢查試卷基本資料編號是否為空白
        if (empty ($intEBId)) {
            throw new Exception('缺少試卷基本資料編號！');
        }

        $bolRtn = false;
        $sql_ExamBasicInfo = 'SELECT `UserId`, `EBCreated` FROM `ExamBasic` WHERE `EBId`=' . $intEBId;
        $rs_ExamBasicInfo = $this->mysqli->query($sql_ExamBasicInfo);
        if(!$rs_ExamBasicInfo){
            throw new Exception('設定施測時，查詢試卷基本資料錯誤！');
        }else{
            $row_ExamBasicInfo = $rs_ExamBasicInfo->fetch_row();
        }

        $strHashCode = MD5('User' . $row_ExamBasicInfo[0] . $row_ExamBasicInfo[1]);

        $sql_setToTest = 'UPDATE `ExamBasic` SET `EBLock`=1, `EBHashCode`=\'' . $strHashCode . '\' WHERE `EBId`=' . $intEBId;
        $rs_setToTest = $this->mysqli->query($sql_setToTest);
        if(!$rs_setToTest){
            throw new Exception('設定施測時錯誤！');
        }else{
            $bolRtn = true;
        }

        return $bolRtn;
    }

    /**
     *
     * @since version 1.2 (2010-06-01)
     * @param String $strHashCode
     * @return Integer ExamBasic Id
     */
    public function getExamIdByHashCode($strHashCode){
        //檢查雜亂編碼是否為空白
        if (empty ($strHashCode)) {
            throw new Exception('缺少試卷雜亂編碼！');
        }

        $sql_getEBId = 'SELECT `EBId` FROM `ExamBasic` WHERE `EBHashCode`=\'' . $strHashCode . '\'';
        $rs_getEBId = $this->mysqli->query($sql_getEBId);
        $row_getEBId = $rs_getEBId->fetch_row();

        return $row_getEBId[0];
    }
    /**
     *
     * @return String
     */
    private function getNowTime() {
        return date('Y-m-d H:i:s');
    }

    function  __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->mysqli = FALSE;
        }
    }
}
?>
